package com.carlwang.util;

import java.awt.Rectangle;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Iterator;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.junit.Test;

import com.itextpdf.text.Document;
import com.itextpdf.text.DocumentException;
import com.itextpdf.text.Font;
import com.itextpdf.text.PageSize;
import com.itextpdf.text.Paragraph;
import com.itextpdf.text.Phrase;
import com.itextpdf.text.pdf.BaseFont;
import com.itextpdf.text.pdf.PdfPCell;
import com.itextpdf.text.pdf.PdfPTable;
import com.itextpdf.text.pdf.PdfWriter;

import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;

public class ReadExcel {

	public Sheet[] readExcel(String pPath) {
		InputStream input;
		try {
			input = new FileInputStream(pPath);
			Workbook wb = Workbook.getWorkbook(input);
			return wb.getSheets();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (BiffException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return null;
	}

	public static void main(String args[]) {
		try {
			InputStream input = new FileInputStream("d:\\test.xls");
			Document pdf = new Document(PageSize.A2);
		
			PdfWriter writer = PdfWriter.getInstance(pdf, new FileOutputStream(
					new File("d:\\test.pdf")));
			pdf.open();
			BaseFont font = BaseFont.createFont(
					"C:\\Windows\\Fonts\\simhei.ttf", BaseFont.IDENTITY_H,
					BaseFont.NOT_EMBEDDED);
			Font currentFont = new Font(font, 16);
			POIFSFileSystem file = new POIFSFileSystem(input);
			HSSFWorkbook book = new HSSFWorkbook(file);
			int num = book.getNumberOfSheets();
			HSSFSheet sheet;
			for (int i = 0; i < num; i++) {
				sheet = book.getSheetAt(i);
				Iterator<Row> iter = sheet.rowIterator();
				HSSFRow row = null;
				PdfPTable table = new PdfPTable(10);
				table.setWidthPercentage(100);
				while (iter.hasNext()) {
					row = (HSSFRow) iter.next();
					Iterator<Cell> cells = row.cellIterator();
					System.out.println(row.getRowNum() + "******************");
					HSSFCell cell;
					while (cells.hasNext()) {
						cell = (HSSFCell) cells.next();
						switch (cell.getCellType()) {
						case HSSFCell.CELL_TYPE_NUMERIC:
							table.addCell(new PdfPCell(new Phrase(cell
									.getNumericCellValue() + "", currentFont)));
							// pdf.add(new Paragraph(cell.getNumericCellValue()
							// + "",currentFont));
							break;
						case HSSFCell.CELL_TYPE_STRING:
							table.addCell(new PdfPCell(new Phrase(cell
									.getStringCellValue() + "", currentFont)));
							// pdf.add(new
							// Paragraph(cell.getStringCellValue(),currentFont));
							break;
						case HSSFCell.CELL_TYPE_BOOLEAN:
							break;
						case HSSFCell.CELL_TYPE_FORMULA:
							break;
						case HSSFCell.CELL_TYPE_BLANK:
							table.addCell(new PdfPCell(new Phrase("          ",
									currentFont)));
							// pdf.add(new Paragraph("       "));
							break;
						default:
							// System.out.println("unsuported sell type");
							break;
						}
					}
					table.completeRow();

				}
				pdf.add(table);
			}

			
			pdf.close();
		} catch (FileNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (DocumentException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	@Test
	public void testRead() {
		readExcel("d:\\test.xls");
	}

}
